package com.saishi.dao.impl;

import com.saishi.dao.QuestionDao;
import com.saishi.entity.Question;
import com.saishi.util.AliPool;
import com.saishi.util.AliPool_yuanchen;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class QuestionDaoImpl implements QuestionDao {
    QueryRunner qr = new QueryRunner(AliPool.getDs());

    QueryRunner qr2 = new QueryRunner(AliPool_yuanchen.getDs());

    @Override
    public int insert(Question question) throws SQLException {
        String sql = "INSERT INTO `question` VALUES(null,?,?,?,?,?,now());";
        return qr.update(sql, question.getQuestionname(), question.getQuestionscore(), question.getQuestiontype(), question.getQuestionoptions(), question.getQuestionanswer());
    }

    @Override
    public int FindEndId() throws SQLException {
        String sql = "SELECT * FROM `question`;";
        Question question = null;
        List<Question> query = qr.query(sql, new BeanListHandler<Question>(Question.class));
        Question question1 = query.get(query.size() - 1);
        if (question1 == null)
            return 0;
        else
            return question1.getQuestionid();
    }

    @Override
    public List<Question> bookQuery(int bookid) throws SQLException {
        String sql = "SELECT * FROM `book` as b inner join `booksquestion` as bq on b.bookid = bq.bqbookid inner join `question` as q on bq.bqquestionid = q.questionid where b.bookid = ?;";
        return qr.query(sql, new BeanListHandler<Question>(Question.class), bookid);
    }

    @Override
    public List<Question> bookQuery(int bookid, int limitStart, int limitEnd) throws SQLException {
        String sql = "SELECT * FROM `book` as b inner join `booksquestion` as bq on b.bookid = bq.bqbookid inner join `question` as q on bq.bqquestionid = q.questionid where b.bookid = ? limit ?,?;";
        return qr.query(sql, new BeanListHandler<Question>(Question.class), bookid, limitStart, limitEnd);
    }

    @Override
    public int[] delete(Object[][] obj) throws SQLException {
        String sql = "DELETE FROM `question` WHERE questionid = ?;";
        return qr.batch(sql, obj);
    }

    @Override
    public List<Question> selecttall() throws SQLException {
        String sql = "select * from question limit 20";
        return qr2.query(sql, new BeanListHandler<Question>(Question.class));
    }

    @Override
    public List<Question> selectt20(int num, int sum) throws SQLException {
        String sql = "select * from question limit ?,?";
        return qr2.query(sql, new BeanListHandler<Question>(Question.class), num, sum);
    }

    @Override
    public int seleQuesId(String question) throws SQLException {
        String sql ="select questionid from question where questionname=?";
//        return  qr.query(sql,new ScalarHandler<Integer>(),question);
        Connection conn  =AliPool.getConn();
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,question);
        ResultSet rs=pst.executeQuery();
        int ids = 0 ;
        if (rs.next()){
            ids = rs.getInt(1);
        }
        pst.close();
        conn.close();
        return ids;
    }

    @Override
    public Question seleQuery(int queryid) throws SQLException {
        String sql = "SELECT * FROM question where questionid = ?";
        return qr.query(sql, new BeanHandler<Question>(Question.class), queryid);
    }



}
